﻿using System;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using System.IO;
using SN.Models;
using SN.Data;

namespace SN.Report
{
    public partial class FormTemp : DockContent
    {
        public int startCol = 0, startRow = 0, endCol = 0, endRow = 0;
        public int _x = 0, _y = 0;//当前单元格偏移量
        public string AutoAddFormula = "";//自动填充公式的公式
        private r_Report report;
        public r_Report Report
        {
            get { return report; }
            set { report = value; }
        }
        public BatchInputFormulaDlg Formula; //指标公式
        public WarningFormualDlg WarinFrm;
        public JYFormulaDlg YJFormula; //校验公式 
        public FormAutoAddFormula FormAutoAddFormula;
        public Boolean isEdit; //是否编辑
        public FormTemp()
        {
            InitializeComponent();
            //为控件添加双击事件，当双击后，将单元格数据自动回填到公式编辑器中。
            this.cellControl1.axCell.MouseDClick += new AxCELL50Lib._DCell2000Events_MouseDClickEventHandler(axCell_MouseDClick);
        }
        void axCell_MouseDClick(object sender, AxCELL50Lib._DCell2000Events_MouseDClickEvent e)
        {
            string sFormula = string.Empty;
            int iCol = 0;
            int iRow = 0;
            isEdit = true; //修改
            iCol = cellControl1.axCell.GetCurrentCol();
            iRow = cellControl1.axCell.GetCurrentRow();
            if (Formula != null)
            {
                if (!string.IsNullOrWhiteSpace(Formula.Text))
                {
                    sFormula = Formula.richTextBox1.Text + this.cellControl1.axCell.GetCellString(iCol, iRow, cellControl1.axCell.GetCurSheet());
                    Formula.richTextBox1.Text = sFormula;
                    cellControl1.axCell.SetCellTip(startCol, startRow, cellControl1.axCell.GetCurSheet(), sFormula); //标注
                }
            }
            else if (YJFormula != null)
            {
                //校验公式
                if (!string.IsNullOrWhiteSpace(YJFormula.Text))
                {
                    sFormula = YJFormula.richTextBox1.Text + this.cellControl1.axCell.GetCellString(iCol, iRow, cellControl1.axCell.GetCurSheet());
                    YJFormula.richTextBox1.Text = sFormula;
                    //cellControl1.axCell.SetCellBackColor(iCol, iRow, cellControl1.axCell.GetCurSheet(), 11);
                    //cellControl1.axCell.SetCellTip(startCol, startRow, cellControl1.axCell.GetCurSheet(), sFormula); //标注
                }
            }
        }
        public static uint ParseRGB(Color color)
        {
            return (uint)(((uint)color.B << 16) | (ushort)(((ushort)color.G << 8) | color.R));
        }
        public void DeleteReport(ref string Msg)
        {
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    if (Report == null) throw new Exception("报表数据不存在!请选择要删除的报表");
                    //判断是否存在指标数据
                    QueryCreator qc = QueryCreator.NewCreator().From<r_ReportInfo>().AddField(r_ReportInfo._.Info_Id).AddWhere(r_ReportInfo._.Info_ReportID == report.Report_Id);
                    int iCount = trans.Count<r_Zbsj>(r_Zbsj._.Zbsj_InfoID.In(qc));
                    if (iCount > 0) throw new Exception("已存在指标数据，不允许删除!");

                    trans.Delete<r_Report>(r_Report._.Report_Id == report.Report_Id);
                    trans.Delete<r_ReportFormat>(r_ReportFormat._.Format_ReportID == report.Report_Id);
                    trans.Delete<r_Zbzd>(r_Zbzd._.Zbzd_ReportID == report.Report_Id);
                    //
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Msg = ex.Message;
                }
            }
            return;
        }

        public string RenameReport(string newName)
        {
            string Msg = string.Empty;
            if (string.IsNullOrEmpty(newName)) return "报表名称不能为空!";
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    trans.Update<r_Report>(r_Report._.Report_Name, newName, r_Report._.Report_Id == Report.Report_Id);
                    //提交数据
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Msg = ex.Message;
                }
            }
            return Msg;
        }

        public string GetRgbClor(int iColorIndex)
        {
            //return ExcelColor.GetHtmlColor(iColor);
            int num = cellControl1.axCell.GetColor(iColorIndex);
            //string strHxColor = string.Format("#{0}", num.ToString("X"));
            //if (strHxColor.Length != 7)
            //{
            //    return "#ffff00";
            //}
            //Color aColor = System.Drawing.Color.FromArgb(System.Int32.Parse(strHxColor.Substring(1, 2), System.Globalization.NumberStyles.AllowHexSpecifier), System.Int32.Parse(strHxColor.Substring(3, 2), System.Globalization.NumberStyles.AllowHexSpecifier), System.Int32.Parse(strHxColor.Substring(5, 2), System.Globalization.NumberStyles.AllowHexSpecifier));
            //string ss = ColorTranslator.ToHtml(aColor);
            int B = num / 65536;
            int G = (num - B * 65536) / 256;
            int R = num - B * 65536 - G * 256;
            if (R == 0 && G == 0 && B == 0)
            {
                return "#FFFFFF";
            }
            Color acolor = System.Drawing.Color.FromArgb(R, G, B);
            string ss = ColorTranslator.ToHtml(acolor);
            return ss;
        }

        /// <summary>
        /// 指标化
        /// </summary>
        /// <returns></returns>
        public string GenerateZBH()
        {
            //数据化：数据化过的单元格可以存储数据，
            //没有数据化过的单元格存储的数据将不被保存到数据库中。
            //在自由报表定义时需要对每个单元格进行数据化，
            //数据化后每个单元格都有一个唯一的编号，
            //输入数据后根据唯一的编号存放到数据库中，以便其它报表引用，或以后便于查询。
            string Msg = string.Empty;
            string strZbbh = string.Empty;
            if (MessageBox.Show("要进行指标化吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";
            //如果有指标数据，则不允许再进行指标化
            //判断是否存在指标数据
            QueryCreator qc = QueryCreator.NewCreator().From<r_ReportInfo>().AddField(r_ReportInfo._.Info_Id).AddWhere(r_ReportInfo._.Info_ReportID == report.Report_Id);
            int iCount = DBUtility.DB.Count<r_Zbsj>(r_Zbsj._.Zbsj_InfoID.In(qc));
            if (iCount > 0)
            {
                MessageBox.Show("已存在指标数据，不允许指标化!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return "";
            }
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            //开启事务
            try
            {
                SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                isEdit = true;
                //trans.Delete<T_ZBZD>(T_ZBZD._.ZBZD_ReportID == report.Report_ID);
                for (int i = startCol; i <= endCol; i++)
                {
                    for (int j = startRow; j <= endRow; j++)
                    {
                        SN.Services.SNService.Hourglass(true);
                        //
                        strZbbh = SN.Services.SNService.GetLSBH<r_Zbzd>();

                        this.cellControl1.axCell.SetCellString(i, j, this.cellControl1.axCell.GetCurSheet(), "[Calc]" + strZbbh);
                        this.cellControl1.axCell.SetCellInput(i, j, this.cellControl1.axCell.GetCurSheet(), 5);//设置为只读
                        //设置背景颜色
                        int myColor = (int)ParseRGB(Color.BurlyWood);
                        this.cellControl1.axCell.SetCellBackColor(i, j, this.cellControl1.axCell.GetCurSheet(), this.cellControl1.axCell.FindColorIndex(myColor, 1));

                        //指标编号生成规则：[calc]001001 行列
                    }
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
            }
            finally
            {
                SN.Services.SNService.Hourglass(false);
            }
            return Msg;
        }

        #region 生成指标字典
        /// <summary>
        /// 根据指标所在行与列，生成指标字典、做统计分析用
        /// </summary>
        /// <returns></returns>
        public string GenerateZBZD()
        {
            //数据化：数据化过的单元格可以存储数据，
            //没有数据化过的单元格存储的数据将不被保存到数据库中。
            //在自由报表定义时需要对每个单元格进行数据化，
            //数据化后每个单元格都有一个唯一的编号，
            //输入数据后根据唯一的编号存放到数据库中，以便其它报表引用，或以后便于查询。
            string Msg = string.Empty;
            string strZbbh = string.Empty;

            if ((string.IsNullOrEmpty(report.Report_IndexCol)) || string.IsNullOrEmpty(report.Reprot_DimensionRow))
            {
                return "该报表还没有设置报表属性，请设置属性后再进行操作!";
            }
            if (MessageBox.Show("确认要生成指标字典吗【设置的公式将会置空】?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";

            isEdit = true;
            //开启事务
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                    trans.Delete<r_Zbzd>(r_Zbzd._.Zbzd_ReportID == report.Report_Id);
                    for (int i = 1; i <= this.cellControl1.axCell.GetCols(cellControl1.axCell.GetCurSheet()); i++)
                    {
                        for (int j = 1; j <= this.cellControl1.axCell.GetRows(cellControl1.axCell.GetCurSheet()); j++)
                        {
                            SN.Services.SNService.Hourglass(true);

                            string sText = this.cellControl1.axCell.GetCellString(i, j, this.cellControl1.axCell.GetCurSheet());
                            sText = sText.Replace("\r", "").Replace("\n", "").Replace(" ", "");
                            if (sText.IndexOf("[Calc]") >= 0)
                            {
                                //存指标
                                r_Zbzd ZBZD = new r_Zbzd();
                                ZBZD.Zbzd_ReportID = Report.Report_Id;
                                ZBZD.Zbzd_Zbbh = sText.Replace("[Calc]", "");
                                ZBZD.Zbzd_Remark = j.ToString() + "," + i.ToString();//坐标位置 

                                ZBZD.Zbzd_Index = this.cellControl1.axCell.GetCellString(Convert.ToInt16(report.Report_IndexCol), j, this.cellControl1.axCell.GetCurSheet()).Trim(); //指标
                                ZBZD.Zbzd_Dimension = this.cellControl1.axCell.GetCellString(i, Convert.ToInt16(report.Reprot_DimensionRow), this.cellControl1.axCell.GetCurSheet()).Trim().Replace("\r\n", "").Replace(" ", ""); //维度
                                ZBZD.Zbzd_Order = j; //取指标所在列
                                //重新生成指标字典时，获取公式
                                ZBZD.Zbzd_Formula = cellControl1.axCell.GetCellTip(i, j, cellControl1.axCell.GetCurSheet());
                                //加判断
                                if (!string.IsNullOrEmpty(ZBZD.Zbzd_Formula))
                                {
                                    if (ZBZD.Zbzd_Formula.ToLower().Contains("select"))
                                        ZBZD.Zdzd_Gslx = "Sql";
                                    else
                                        ZBZD.Zdzd_Gslx = "Calc";
                                }
                                ZBZD.Detach(); //新增
                                //保存数据
                                trans.Save<r_Zbzd>(ZBZD);

                            }
                            //指标编号生成规则：[calc]001001 行列
                        }
                    }
                    //提交事务
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Msg = ex.Message;
                }
                finally
                {
                    SN.Services.SNService.Hourglass(false);
                }
            }

            return Msg;

        }
        #endregion

        #region 生成Web格式
        /// <summary>
        /// 生成Web格式
        /// </summary>
        /// <returns></returns>
        public string GenerateWebFormat()
        {
            string Msg = string.Empty;
            //全选表格
            //this.cellControl1.axCell.SelectRange(0, 0, this.cellControl1.axCell.GetRows(this.cellControl1.axCell.GetCurSheet()), this.cellControl1.axCell.GetCols(this.cellControl1.axCell.GetCurSheet()));
            if (MessageBox.Show("确认要进行Web化吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";
            isEdit = true;
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            decimal totalcell = (endRow - startRow + 1) * (endCol - startCol + 1);
            decimal nowcell = 0;
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    //保存Reprot属性行高与列宽
                    StringBuilder sRowHeight = new StringBuilder();
                    string sColWidth = "";
                    string sCurRowHeight = "";
                    for (int j = startCol; j < endCol + 1; j++) //循环列宽
                    {
                        sColWidth = sColWidth + "," + this.cellControl1.axCell.GetColWidth(1, j, this.cellControl1.axCell.GetCurSheet()).ToString(); //列宽
                    }
                    SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                    //先删除本报表已生成的web格式数据
                    trans.Delete<r_ReportContents>(r_ReportContents._.Contents_ReportId == Report.Report_Id);
                    for (int i = startRow; i < endRow + 1; i++)
                    {
                        sCurRowHeight = this.cellControl1.axCell.GetRowHeight(1, i, this.cellControl1.axCell.GetCurSheet()).ToString();
                        sRowHeight.Append("," + sCurRowHeight);

                        for (int j = startCol; j < endCol + 1; j++)
                        {
                            this.cellControl1.axCell.SelectRange(j, i, j, i);
                            int scol = 0, ecol = 0, srow = 0, erow = 0;
                            this.cellControl1.axCell.GetSelectRange(ref scol, ref srow, ref ecol, ref erow);

                            int rowspan = erow - srow + 1;//合并行
                            int colspan = ecol - scol + 1;//合并列
                            if (i == srow && j == scol)
                            {
                                //开始写数据库信息
                                r_ReportContents ReportContents = new r_ReportContents();
                                ReportContents.Contents_ReportId = Report.Report_Id;
                                string text = this.cellControl1.axCell.GetCellString(scol, srow, cellControl1.axCell.GetCurSheet());
                                text = text.Replace("\r\n", "<br/>"); //替换web中的换行符
                                text = text.Replace(System.Text.Encoding.ASCII.GetString(new Byte[1] { 32 }), "&nbsp;");//替换英文空格

                                // ss = ss.Replace(" ", "&nbsp;");
                                int a = text.Length;
                                text = text.TrimStart(' '); //取字符前的空格数，即缩进数
                                int b = text.Length;
                                if (text.IndexOf("[Calc]") >= 0 && string.IsNullOrEmpty(cellControl1.axCell.GetCellTip(scol, srow, cellControl1.axCell.GetCurSheet())))
                                {
                                    //SetCellTip
                                    //可以编辑
                                    ReportContents.Contents_IsEdit = "1";
                                }
                                else
                                {
                                    //不允许编辑
                                    //ColorTranslator.ToHtml(Color.Red 
                                    ReportContents.Contents_IsEdit = "0";
                                    int iColorIndex = this.cellControl1.axCell.GetCellBackColor(scol, srow, cellControl1.axCell.GetCurSheet());
                                    string ss = GetRgbClor(iColorIndex);
                                    ReportContents.Contents_BorderStyle += string.Format("background-color:{0};", ss);
                                    //";background-color:" +gray;";
                                }
                                //有公式的情况下
                                ReportContents.Contents_Text = text; //内容
                                ReportContents.Contents_Indentation = (a - b).ToString(); //缩进
                                ReportContents.Contents_Row = srow;
                                ReportContents.Contents_RowSpan = rowspan;
                                ReportContents.Contents_Col = scol;
                                ReportContents.Contents_ColSpan = colspan;
                                //
                                int FontStyle = this.cellControl1.axCell.GetCellFontStyle(scol, srow, this.cellControl1.axCell.GetCurSheet());
                                switch (FontStyle)
                                {
                                    case 2: //粗体
                                        ReportContents.Contents_FontBold = "font-weight:bold";
                                        break;
                                    case 4://斜体 
                                        ReportContents.Contents_FontItalic = "font-style:italic";
                                        break;
                                    case 8: //下滑线
                                        ReportContents.Contents_FontUnderline = "text-decoration:underline";
                                        break;
                                    case 16://删除线
                                        ReportContents.Contents_FontStrikeout = "text-decoration:line-through";
                                        break;
                                    default:
                                        break;
                                }
                                int FontAlign = cellControl1.axCell.GetCellAlign(scol, srow, cellControl1.axCell.GetCurSheet());
                                switch (FontAlign)
                                {
                                    case 1://左对齐
                                        ReportContents.Contents_H = "text-align:left";
                                        ReportContents.Contents_V = "";
                                        break;
                                    case 2://右对齐
                                        ReportContents.Contents_H = "text-align:right";
                                        ReportContents.Contents_V = "";
                                        break;
                                    case 4: //水平居中
                                        ReportContents.Contents_H = "text-align:center";
                                        ReportContents.Contents_V = "";
                                        break;
                                    case 8://居上对齐
                                        ReportContents.Contents_H = "";
                                        ReportContents.Contents_V = "vertical-align:text-top";
                                        break;
                                    case 16://居下对齐
                                        ReportContents.Contents_H = "";
                                        ReportContents.Contents_V = "vertical-align:text-bottom";
                                        break;
                                    case 32://垂直居中
                                        ReportContents.Contents_H = "";
                                        // ReportContents.Contents_V = "vertical-align:text-middle";
                                        ReportContents.Contents_V = "vertical-align:middle";

                                        break;
                                    case 9:
                                        ReportContents.Contents_H = "text-align:left";
                                        ReportContents.Contents_V = "vertical-align:text-top";
                                        break;
                                    case 17:
                                        ReportContents.Contents_H = "text-align:left";
                                        ReportContents.Contents_V = "vertical-align:text-bottom";
                                        break;
                                    case 33:
                                        ReportContents.Contents_H = "text-align:left";
                                        ReportContents.Contents_V = string.Format("vertical-align:text-middle;line-height:{0}px", sCurRowHeight.Replace(',', ' '));
                                        break;
                                    case 10:
                                        ReportContents.Contents_H = "text-align:right";
                                        ReportContents.Contents_V = "vertical-align:text-top";
                                        break;
                                    case 12:
                                        ReportContents.Contents_H = "text-align:center";
                                        ReportContents.Contents_V = "vertical-align:text-top";
                                        break;
                                    case 18:
                                        ReportContents.Contents_H = "text-align:right";
                                        ReportContents.Contents_V = "vertical-align:text-bottom";
                                        break;
                                    case 20://水平居中 
                                        ReportContents.Contents_H = "text-align:center";
                                        ReportContents.Contents_V = "vertical-align:text-bottom";
                                        break;
                                    case 34://
                                        ReportContents.Contents_H = "text-align:right";
                                        ReportContents.Contents_V = "vertical-align:text-middle";
                                        break;
                                    case 36://水平居中 加垂直居中
                                        ReportContents.Contents_H = "text-align:center";
                                        //ReportContents.Contents_V = "vertical-align:text-middle";
                                        ReportContents.Contents_V = "vertical-align:middle";
                                        break;
                                    default:
                                        break;
                                }
                                string FontName = cellControl1.axCell.GetFontName(cellControl1.axCell.GetCellFont(scol, srow, cellControl1.axCell.GetCurSheet()));
                                ReportContents.Contents_FontName = FontName; //设置字体

                                float FontSize = cellControl1.axCell.GetCellFontSize(scol, srow, cellControl1.axCell.GetCurSheet());
                                ReportContents.Contents_FontSize = Convert.ToInt16(FontSize); //设置字体大小
                                //边框
                                int iBorder = 0;
                                int iBorderClr = 0;
                                string sBorderStyle = "";
                                string strClr = "";
                                Color c;
                                //左
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //左
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //左
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                // strClr = GetHtmlClr(iBorderClr);
                                //sBorderStyle = "border-left:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                sBorderStyle = "border-left:" + "1.5px solid " + strClr + ";";
                                //上
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                //sBorderStyle += "border-top:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                sBorderStyle += "border-top:" + "1.5px solid " + strClr + ";";
                                //右
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                //sBorderStyle += "border-right:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                sBorderStyle += "border-right:" + "1.5px solid " + strClr + ";";
                                //下
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                //sBorderStyle += "border-bottom:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                sBorderStyle += "border-bottom:" + "1.5px solid " + strClr + ";";
                                ReportContents.Contents_BorderStyle += sBorderStyle;
                                //添加到数据库
                                //得到单元格格式
                                int iDigital = 0;
                                int iNumType = this.cellControl1.axCell.GetCellNumType(scol, srow, this.cellControl1.axCell.GetCurSheet());
                                string sFormat = "";
                                //存储时，0 代表字符 1 代表数值
                                switch (iNumType)
                                {
                                    case 0: //缺省
                                    case 7: //文本
                                        sFormat = "0";//字符串
                                        break;
                                    case 1://普通数值 
                                    case 2://货币
                                    case 8://货币大写
                                        iDigital = this.cellControl1.axCell.GetCellDigital(scol, srow, this.cellControl1.axCell.GetCurSheet()); //As Long取得小数位
                                        //iHideZone = this.cellControl1.axCell.GetCellHideZero(scol, srow, this.cellControl1.axCell.GetCurSheet()); //As Long 得到指定单元格是否隐藏 0值(0不隐藏 默认值0)。 
                                        sFormat = string.Format("{0},{1}", iNumType, iDigital);

                                        break;
                                    default:
                                        sFormat = "0"; //按字符串处理
                                        break;
                                }
                                //1 文本单行显示，当长度超过单元格宽度时，超过部分将被遮挡。
                                //2 多行显示，文本自动换行，多行显示。
                                //3 省略显示，文本单行显示，超出单元格宽度范围的文本将被"..."所代替。
                                //4 省略显示，文本单行显示，整个单元格的文本将被"#####"所代替。 
                                int istyle = this.cellControl1.axCell.GetCellTextStyle(scol, srow, this.cellControl1.axCell.GetCurSheet());
                                if (istyle == 2)
                                {
                                    sFormat += "table-layout:fixed; word-break:break-all;";
                                }

                                ReportContents.Contents_Format = sFormat;
                                ReportContents.Detach();
                                trans.Save<r_ReportContents>(ReportContents);

                            }
                            j = j + colspan - 1;//如果合并列的话改变列索引
                            nowcell = nowcell + 1 + colspan - 1;
                        }
                    }

                    this.cellControl1.axCell.SelectRange(startCol, startRow, endCol, endRow);
                    //更新Report 行高与列宽 与是否生成Web格式
                    trans.Update<r_Report>(new Field[] { r_Report._.Report_RowHeight, r_Report._.Report_ColWidth, r_Report._.Report_IsWeb },
                                           new object[] { sRowHeight.ToString().Substring(1), sColWidth.Substring(1), "1" },
                                           r_Report._.Report_Id == Report.Report_Id);

                    //提交事务
                    trans.Commit();
                    SN.Services.SNService.Hourglass(false); //设置图标为正常

                    MessageBox.Show("Web化完成!", "提示!", MessageBoxButtons.OK);
                }
                catch (Exception ex)
                {
                    SN.Services.SNService.Hourglass(false); // 设置图标为正常
                    trans.Rollback();

                    Msg = ex.Message;
                }
            }
            return Msg;
        }
        #endregion

        //写文件
        public string WriteBinaryData()
        {
            if (report == null) throw new Exception("报表参数不能为空!");
            if (this.cellControl1.axCell.GetTotalSheets() > 1)
            {
                return "目前只允许有一个工作页(sheet)";
            }
            string Msg = string.Empty;
            try
            {
                object buffer = null;
                cellControl1.axCell.SaveToBuffer(ref buffer);
                //开始保存数据
                using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
                {
                    try
                    {
                        if (string.IsNullOrEmpty(Report.Report_Id))
                        {
                            //新增
                            Report.Report_Id = SN.Services.SNService.GetLSBH<r_Report>();
                            Report.Report_CreateDate = DateTime.Now.Date;
                            report.Report_UpdateDate = DateTime.Now.Date;
                            report.Report_IsWeb = "0";//是否Web
                            //新增
                            Report.Detach();
                        }
                        else
                        {
                            report.Report_UpdateDate = DateTime.Now.Date; //更新的数据内容
                            //更新
                            Report.Attach();
                        }
                        //更新
                        trans.Save<r_Report>(Report);
                        //先删除T_ReportFormat
                        trans.Delete<r_ReportFormat>(r_ReportFormat._.Format_ReportID == Report.Report_Id);
                        r_ReportFormat ReportFormat = new r_ReportFormat();
                        ReportFormat.Format_ReportID = Report.Report_Id;
                        ReportFormat.Format_Format = (byte[])buffer; //GetBinaryFormatData(buffer);// bt;
                        ReportFormat.Detach();
                        trans.Save<r_ReportFormat>(ReportFormat);
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        Msg = ex.Message;
                    }
                    //更新数据
                    trans.Commit();
                    isEdit = false; //修改编辑状态为未编辑
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                MessageBox.Show(ex.Message);
            }
            //}
            return Msg;
        }
        //读取文件
        public void ReadBinaryData(string ReportID)
        {
            r_ReportFormat reportFormat = SN.Data.DBUtility.DB.From<r_ReportFormat>().Where(r_ReportFormat._.Format_ReportID == ReportID).ToSingle();
            if (reportFormat == null) return;
            byte[] bt = reportFormat.Format_Format;
            this.cellControl1.axCell.ReadFromBuffer(bt);

            //隐藏打印分界线
            cellControl1.axCell.ShowPageBreak(0);
            //可以理解为重新加载华表 
            cellControl1.axCell.ReDraw();
            //显示列
            cellControl1.axCell.ShowTopLabel(1, cellControl1.axCell.GetCurSheet());
            //显示行
            cellControl1.axCell.ShowSideLabel(1, cellControl1.axCell.GetCurSheet());

        }

        private void FormTemp_Load(object sender, EventArgs e)
        {
        }

        private void 批量输入公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //先判断指标编号是不是已经生成
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            //if (startCol != endCol || startRow != endRow)
            //{
            //    MessageBox.Show("请选中一个单元格", "提示!");
            //    return;
            //}
            //判断该指标是不是已经指标化
            string sZbbh = this.cellControl1.axCell.GetCellString(startCol, startRow, this.cellControl1.axCell.GetCurSheet());
            if (sZbbh.IndexOf("[Calc]") == -1)
            {
                MessageBox.Show("还没有指标化，不允许定义公式!");
                return;
            }
            isEdit = true;

            //指标编号 存的没有[Calc]
            sZbbh = sZbbh.Replace("[Calc]", "");
            r_Zbzd ZBZD = SN.Data.DBUtility.DB.From<r_Zbzd>().Where(r_Zbzd._.Zbzd_ReportID == report.Report_Id && r_Zbzd._.Zbzd_Zbbh == sZbbh).ToSingle();
            if (ZBZD == null)
            {
                MessageBox.Show("在指标字典中没有找到该报表的指标！", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            ZBZD.Zbzd_Zbbh = sZbbh;
            //指标字典
            Formula = new BatchInputFormulaDlg();
            Formula.FrmTemp = this;
            Formula.Zbzd = ZBZD;
            //
            Formula.Show();

            //Formula.Dispose();
            //if (Formula.ShowDialog() == DialogResult.OK)
            //{
            //    //设置公式属性
            //    this.cellControl1.axCell.SetCellTip(startCol, startRow, cellControl1.axCell.GetCurSheet(), "指标公式:" + ZBZD.ZBZD_Formula);
            //    //int myColor = (int)ParseRGB(Color.MediumSlateBlue);
            //    //this.cellControl1.axCell.SetCellBackColor(startCol, startRow, cellControl1.axCell.GetCurSheet(), this.cellControl1.axCell.FindColorIndex(myColor, 1));
            //    ZBZD.Attach();
            //    SN.Data.DBUtility.DB.Save<T_ZBZD>(ZBZD);
            //}//
            //设置单元格备注
        }

        private void 指标化ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateZBH(); //
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
                return;
            }
        }

        private void 生成Web格式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateWebFormat();
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
            }
        }

        private void 生成指标字典ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateZBZD();
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
            }
        }

        private void 报表属性ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormAttribute frmAttr = new FormAttribute();
            frmAttr.Report = this.Report;
            frmAttr.ShowDialog();
        }

        private void 剪切ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.CutRange(startCol, startRow, endCol, endRow);
        }

        private void 复制ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.CopyRange(startCol, startRow, endCol, endRow);
        }

        private void 粘贴ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.Paste(startCol, startRow, 0, 1, 0);
        }

        private void FormTemp_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (isEdit)
            {
                DialogResult dr = MessageBox.Show("是否要保存数据？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (dr == DialogResult.Yes)
                {
                    e.Cancel = true;
                    this.WriteBinaryData();
                    return;
                }
                else
                {
                    e.Cancel = false;
                }
            }
        }

        private void 单元格校验公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //先判断指标编号是不是已经生成
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            if (startCol != endCol || startRow != endRow)
            {
                MessageBox.Show("请选中一个单元格", "提示!");
                return;
            }
            //判断该指标是不是已经指标化
            string sZbbh = this.cellControl1.axCell.GetCellString(startCol, startRow, this.cellControl1.axCell.GetCurSheet());
            if (sZbbh.IndexOf("[Calc]") == -1)
            {
                MessageBox.Show("还没有指标化，不允许定义公式!");
                return;
            }
            isEdit = true;
            //指标编号 存的没有[Calc]
            sZbbh = sZbbh.Replace("[Calc]", "");
            r_Zbzd ZBZD = SN.Data.DBUtility.DB.From<r_Zbzd>().Where(r_Zbzd._.Zbzd_ReportID == report.Report_Id && r_Zbzd._.Zbzd_Zbbh == sZbbh).ToSingle();
            if (ZBZD == null)
            {
                MessageBox.Show("在指标字典中没有找到该报表的指标！");
                return;
            }
            ZBZD.Zbzd_Zbbh = sZbbh;
            //指标字典
            YJFormula = new JYFormulaDlg();
            YJFormula.FrmTemp = this;
            YJFormula.Zbzd = ZBZD;
            //设置 当前选中单元格的x坐标
            YJFormula.currSelectCellX = startCol;
            YJFormula.currSelectCellY = startRow;
            //
            YJFormula.Show();

        }

        private void 上传模版ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            var dialogResult = openFileDialog1.ShowDialog();
            if (dialogResult == System.Windows.Forms.DialogResult.OK)
            {
                r_Report report = DBUtility.DB.Single<r_Report>(r_Report._.Report_Id == this.Report.Report_Id);
                if (report.Report_ExportTemplete != null && report.Report_ExportTemplete.Length > 0)
                {
                    if (MessageBox.Show("系统检测到该报表已存在导出模版，继续操作将会覆盖原模板，是否继续？", "提示", MessageBoxButtons.YesNo,
                          MessageBoxIcon.Question) != System.Windows.Forms.DialogResult.Yes) return;
                }
                string fileName = openFileDialog1.FileName;
                byte[] buffer = null;
                using (System.IO.FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    buffer = new byte[fs.Length];
                    fs.Read(buffer, 0, (int)fs.Length);
                }
                DBUtility.DB.Update<r_Report>(r_Report._.Report_ExportTemplete, buffer, r_Report._.Report_Id == this.Report.Report_Id);
                MessageBox.Show("上传成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void 下载模版ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            r_Report report = DBUtility.DB.Single<r_Report>(r_Report._.Report_Id == this.Report.Report_Id);
            if (report.Report_ExportTemplete != null && report.Report_ExportTemplete.Length > 0)
            {
                SaveFileDialog savefile = new SaveFileDialog();
                FileStream fs = null;
                savefile.FileName = report.Report_Name;
                savefile.Filter = "Excel文件|*.xls";
                if (savefile.ShowDialog() == DialogResult.OK)
                {


                    fs = new FileStream(savefile.FileName, FileMode.Create);
                    BinaryWriter bw = new BinaryWriter(fs);//以二进制方式写文件 

                    bw.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);

                    fs.Close();
                    bw.Close();

                }
                else
                {
                    MessageBox.Show("模版下载失败！");
                }

            }
            else
            {
                MessageBox.Show("该报表没有上传导出模板");
            }
        }

        private void 自动生成校验公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            FormAutoAddFormula = new FormAutoAddFormula();
            FormAutoAddFormula.FrmTemp = this;
            FormAutoAddFormula.ShowDialog();
            if (string.IsNullOrEmpty(AutoAddFormula))
            {
                MessageBox.Show("应用公式不能为空！");
                return;
            }
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            for (int i = startCol; i <= endCol; i++)
            {
                for (int j = startRow; j <= endRow; j++)
                {

                    if (addformula(i, j) == false)
                        break;

                }
            }
        }

        //自动批量添加批量校验公式方法
        public bool addformula(int currSelectCellX, int currSelectCellY)
        {

            string lsFormula = "";
            try
            {
                //判断该指标是不是已经指标化
                string sZbbh = this.cellControl1.axCell.GetCellString(currSelectCellX, currSelectCellY, this.cellControl1.axCell.GetCurSheet());
                if (sZbbh.IndexOf("[Calc]") == -1)
                {
                    MessageBox.Show("还没有指标化，不允许定义公式!");
                    return false;
                }
                isEdit = true;
                //指标编号 存的没有[Calc]
                sZbbh = sZbbh.Replace("[Calc]", "");
                r_Zbzd zbzd = SN.Data.DBUtility.DB.From<r_Zbzd>().Where(r_Zbzd._.Zbzd_ReportID == report.Report_Id && r_Zbzd._.Zbzd_Zbbh == sZbbh).ToSingle();
                if (zbzd == null)
                {
                    MessageBox.Show("在指标字典中没有找到该报表的指标！");
                    return false;
                }
                //zbzd.ZBZD_ZBBH = sZbbh;

                //取得要比较的指标
                string newZbbh = this.cellControl1.axCell.GetCellString(currSelectCellX + _x, currSelectCellY + _y, this.cellControl1.axCell.GetCurSheet());
                lsFormula = AutoAddFormula + newZbbh;

                //判断字符长度不能超过1000
                if (lsFormula.Length > 1000)
                {
                    MessageBox.Show("公式太长啦!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                if (lsFormula.Length <= 1)
                {
                    MessageBox.Show("公式太短啦!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }

                int iSheet = this.cellControl1.axCell.GetCurSheet();
                int myColor = (int)ParseRGB(Color.Yellow); //设置成黄色
                this.cellControl1.axCell.SetCellBackColor(currSelectCellX, currSelectCellY, iSheet, myColor);
                //写开始行 与开始列 （即指标公式所在的行与列)
                //FrmTemp.cellControl1.axCell.SetCellTip(FrmTemp.startCol, FrmTemp.startRow, iSheet, this.richTextBox1.Text);
                //保存数据
                zbzd.Zbzd_Formula = lsFormula;
                zbzd.Attach();
                SN.Data.DBUtility.DB.Save<r_Zbzd>(zbzd);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }
        //批量生成公式




        private string cellControl1_eventGeneralZB(int isRow, int isCol, int ieRow, int ieCol)
        {
            //指标化
            string Msg = string.Empty;
            Msg = GenerateZBH(); //
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
            }
            return default(string);

        }

        private void cellControl1_eventClearFormula(int isRow, int isCol, int ieRow, int ieCol)
        {
            //清除指标
            for (int i = isRow; i <= ieRow; i++)
            {
                for (int j = isCol; j <= ieCol; j++)
                {
                    string sZbbh = this.cellControl1.axCell.GetCellString(j, i, this.cellControl1.axCell.GetCurSheet());
                    if (sZbbh.IndexOf("[Calc]") == -1)
                    {
                        continue;
                    }
                    //指标编号 存的没有[Calc]
                    sZbbh = sZbbh.Replace("[Calc]", "");
                    //删除指标公式
                    DBUtility.DB.Update<r_Zbzd>(r_Zbzd._.Zbzd_Formula, "", r_Zbzd._.Zbzd_Zbbh == sZbbh);
                }
            }
        }

        private void 预警条件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //先判断指标编号是不是已经生成
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            //if (startCol != endCol || startRow != endRow)
            //{
            //    MessageBox.Show("请选中一个单元格", "提示!");
            //    return;
            //}
            //判断该指标是不是已经指标化

            string sZbbh = this.cellControl1.axCell.GetCellString(startCol, startRow, this.cellControl1.axCell.GetCurSheet());
            if (sZbbh.IndexOf("[Calc]") == -1)
            {
                MessageBox.Show("还没有指标化，不允许定义公式!");
                return;
            }
            isEdit = true;

            //指标编号 存的没有[Calc]
            sZbbh = sZbbh.Replace("[Calc]", "");
            r_Zbzd ZBZD = SN.Data.DBUtility.DB.From<r_Zbzd>().Where(r_Zbzd._.Zbzd_ReportID == report.Report_Id && r_Zbzd._.Zbzd_Zbbh == sZbbh).ToSingle();
            if (ZBZD == null)
            {
                MessageBox.Show("在指标字典中没有找到该报表的指标！", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            ZBZD.Zbzd_Zbbh = sZbbh;
            //指标字典
            WarinFrm = new WarningFormualDlg();
            WarinFrm.FrmTemp = this;
            WarinFrm.Zbzd = ZBZD;
            //
            WarinFrm.Show();


        }

        //批量生成公式
        private string cellControl1_eventbatchCreateGs(int isRow, int isCol, int ieRow, int ieCol)
        {
            if (!report.Report_Name.Contains("Fe含量") && !report.Report_Name.Contains("Si含量") && !report.Report_Name.Contains("原铝品位"))
            {
                MessageBox.Show("不允许定义");
                return "";
            }
            for (int i = isRow; i <= ieRow; i++)
            {
                for (int j = isCol; j <= ieCol; j++)
                {
                    string sZbbh = this.cellControl1.axCell.GetCellString(j, i, this.cellControl1.axCell.GetCurSheet());
                    if (sZbbh.IndexOf("[Calc]") == -1)
                    {
                        continue;
                    }
                    //指标编号 存的没有[Calc]
                    sZbbh = sZbbh.Replace("[Calc]", "");
                    r_Zbzd zbzd = SN.Data.DBUtility.DB.From<r_Zbzd>().Where(r_Zbzd._.Zbzd_ReportID == report.Report_Id && r_Zbzd._.Zbzd_Zbbh == sZbbh).ToSingle();
                    string sCh = cellControl1.axCell.GetCellString(1, i, cellControl1.axCell.GetCurSheet());
                    string sDay = cellControl1.axCell.GetCellString(j, 2, cellControl1.axCell.GetCurSheet());
                    string lsFormula = "";
                    if (report.Report_Name.Contains("Fe含量"))
                    {
                        lsFormula = string.Format("Fe含量({0},{1})", sCh, sDay);
                    }
                    else if (report.Report_Name.Contains("Si含量"))
                    {
                        lsFormula = string.Format("Si含量({0},{1})", sCh, sDay);
                    }
                    else if (report.Report_Name.Contains("原铝品位"))
                    {
                        //Get原铝品味_Sql
                        lsFormula = string.Format("原铝品位({0},{1})", sCh, sDay);
                    }
                    //
                    cellControl1.axCell.SetCellTip(j, i, cellControl1.axCell.GetCurSheet(), lsFormula); //标注
                    if (zbzd != null)
                    {
                        zbzd.Zdzd_Gslx = "Sql";
                        zbzd.Zbzd_Formula = lsFormula;
                        zbzd.Attach();
                        SN.Data.DBUtility.DB.Save<r_Zbzd>(zbzd);
                    }
                }

            }
            MessageBox.Show("完成");
            return default(string);
        }


    }
}
